package com.quanxi.qxaimain.controller.v1.indicators;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.serializer.SerializerFeature;
import com.google.gson.JsonObject;
import com.quanxi.qxaimain.common.globalException.GlobalException;
import com.quanxi.qxaimain.common.globalException.NotControllerResponseAdvice;
import com.quanxi.qxaimain.common.globalException.ResponseVo;
import com.quanxi.qxaimain.common.globalException.ResultCode;
import com.quanxi.qxaimain.common.message.ServerConfig;
import com.quanxi.qxaimain.common.message.ymlConfig;
import com.quanxi.qxaimain.common.util.RestTemplateUtils.RestTemplateUtils;

import com.quanxi.qxaimain.common.util.execlExporttool.SXSSFWorkbookExporter;
import com.quanxi.qxaimain.common.util.sqlFormat.sqlFormat;
import com.quanxi.qxaimain.common.util.time.DateUtils;
import com.quanxi.qxaimain.dao.indicators.indicatorsDataMapper;
import com.quanxi.qxaimain.dao.insertDemo.insertDemoMapper;
import com.quanxi.qxaimain.dao.produce.produceMapper;
import com.quanxi.qxaimain.model.indicators.Pargram;
import com.quanxi.qxaimain.model.indicators.indicatorsGet;
import com.quanxi.qxaimain.model.indicators.responseIndicators;
import com.quanxi.qxaimain.service.downloadExcelServer.downloadExcelServer;
import com.quanxi.qxaimain.service.hystrixServer.hystrixServer;
import com.quanxi.qxaimain.service.indicatorsService.indicatorsAsynService;
import com.quanxi.qxaimain.service.indicatorsService.indicatorsService;
import com.sargeraswang.util.ExcelUtil.ExcelLogs;
import com.sargeraswang.util.ExcelUtil.ExcelUtil;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiImplicitParam;
import io.swagger.annotations.ApiImplicitParams;
import io.swagger.annotations.ApiOperation;
import lombok.extern.slf4j.Slf4j;
import net.sf.json.JSONArray;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.session.ResultContext;
import org.apache.ibatis.session.ResultHandler;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.util.LinkedMultiValueMap;
import org.springframework.util.MultiValueMap;
import org.springframework.web.bind.annotation.*;
import com.alibaba.fastjson.JSONObject;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.concurrent.ExecutionException;
import java.util.concurrent.Future;
import java.util.function.UnaryOperator;
import java.util.stream.Collectors;

import static com.quanxi.qxaimain.common.globalException.ResultCode.SUCCESS;

/**
 * @Author lyx
 * @create 2022/8/30 18:43
 * 指标sql计算
 */
@Slf4j
@RestController
@RequestMapping("/indicators")
@Api(tags = "指标")
public class indicatorsDataController {
    @Resource
    private ymlConfig ymlCofig;

    @Resource
    private   indicatorsService indicatorsService;
    @Resource
    private indicatorsDataMapper indicatorsDataMapper;
    @Resource
    private    indicatorsGet indicatorsGet;
    @Resource
    private  indicatorsAsynService indicatorsAsynService;
    @Autowired
    private ServerConfig serverConfig;
    @Resource
    private hystrixServer hystrixServer;
    @Resource
    private downloadExcelServer downloadExcelServer;
    @Resource
    private produceMapper produceMapper;



    @NotControllerResponseAdvice   //自定义返回封装 ，不走全局统一
    @ApiOperation(value = "指标sql批量接口（仅供新建层和指标查看前端页面）",  notes = "")
    @PostMapping(value = "/sqlDataSetList")
    @ApiImplicitParams({@ApiImplicitParam(name = "params" ,value = "参数说明：'startTime(2020-03-01 00:00:00) endTime(2020-04-01 00:00:00) indIdList(指标编号数组List)  mqFlag（true）开启消息队列方式",required = true,dataType = "",paramType = "body")})
    public  Object  sqlDataSetList(@RequestBody JSONObject params) throws GlobalException, ExecutionException, InterruptedException {
              List<String> indIdList=JSONObject.parseArray(params.getJSONArray("indIdList").toString(),String.class);
              String D1=params.getString("startTime");
              String D2=params.getString("endTime");
              boolean mqFlag=params.getBoolean("mqFlag");//是否开启消息队列方式
              String dataSource="db1";
              String table="qxzsk_t_indicator_sql";


         List<Map<String, Object>> indicatorsLists = indicatorsService.indicatorsLists(table, indIdList);
         List <Object> result= new ArrayList<>();
         Pargram Pargram = new Pargram(D1,D2);


        long starttime = System.currentTimeMillis();
        List< Future<responseIndicators>> listFutureresponseIndicators= new ArrayList<>();
        for (int i = 0; i <indicatorsLists.size() ; i++) {
            String ind_id = indicatorsLists.get(i).get("ind_id").toString();
            String ind_name = indicatorsLists.get(i).get("ind_name").toString();
            String sql_total = indicatorsLists.get(i).get("sql_total").toString();
          //  responseIndicators responseIndicators = new responseIndicators(ind_id,ind_name,sql_total,indicatorsGet,Pargram);
            //多线程异步调用sql
            Future<responseIndicators> responseIndicatorsFuture = indicatorsAsynService.get(ind_id, ind_name, sql_total, indicatorsGet, Pargram);
            listFutureresponseIndicators.add(responseIndicatorsFuture);//将异步结果加入list中,注意不能在这里直接获取异步的结果，否则线程将阻塞,异步无效
        }


       // 异步返回结果整理
        List<String> listSqlsuccess = new ArrayList<>();
        List<String> listSqlerror= new ArrayList<>();
        List<String> listSqlovertime= new ArrayList<>();
        List<String> listSqlreturn= new ArrayList<>();

        for (int i = 0; i <listFutureresponseIndicators.size() ; i++) {
            responseIndicators responseIndicators = listFutureresponseIndicators.get(i).get();
            responseIndicators.setSql_result(responseIndicators.getSql_result());
            listSqlreturn.add(responseIndicators.getInd_id().toUpperCase());//返回个数

            Enum<ResultCode> sql_message=  (Enum<ResultCode>)responseIndicators.getSql_message();
            int code = ((ResultCode) sql_message).getCode();
            if (code==ResultCode.SQL_SUCCESS.getCode()){
                listSqlsuccess.add(responseIndicators.getInd_id().toUpperCase());//sql正确list 转大写
                result.add(responseIndicators);
            }
            if (code==ResultCode.SQL_ERROR.getCode()){
                listSqlerror.add(responseIndicators.getInd_id().toUpperCase());//语法错误指标
            }
            if (code==ResultCode.SQL_OVER_TIME_60S.getCode()){
                listSqlovertime.add(responseIndicators.getInd_id().toUpperCase());//
                result.add(responseIndicators);//超时的也返回结果
            }
//            if ( Integer.valueOf(responseIndicators.getTime())>=60000){
//                listSqlovertime.add(responseIndicators.getInd_id().toUpperCase());//超时60s的指标
//            }

        }

        JSONObject resJson = new JSONObject(new LinkedHashMap());
        resJson.put("时间",System.currentTimeMillis()-starttime+"ms");
        resJson.put("请求个数",indIdList.size());
        resJson.put("成功个数",listSqlsuccess.size());
        resJson.put("sql语法错误个数",listSqlerror.size()+" "+listSqlerror);
        resJson.put("sql超过60s个数",listSqlovertime.size()+" "+listSqlovertime);
        List<String> notExistslist = indIdList.stream().map(String::toUpperCase).collect(Collectors.toList());//转大写
        notExistslist.removeAll(listSqlreturn);
        resJson.put("指标无效个数 ", +notExistslist.size()+"(包含编号不存在平台上、未通过平台校验编译、超时限制等指标): "+notExistslist);

         log.info(resJson.toJSONString());
        //自定义返回封装 ，不走全局统一
        ResponseVo responseVo = new ResponseVo(SUCCESS.getCode(),resJson,result);
        return responseVo;
    }



    @NotControllerResponseAdvice   //自定义返回封装 ，不走全局统一
    @ApiOperation(value ="指标查看表报",notes = "")
    @PostMapping(value = "/dataReport")
    @ApiImplicitParams({@ApiImplicitParam(name = "params" ,value = "",required = true,dataType = "",paramType = "body")})
    public  Object dataReport(@RequestBody JSONObject params){
        String searchwords=params.getString("searchWords");
        String indicatorsName = params.getString("indicatorsName");
        String hospitalName = params.getString("hospitalName");
        String starttime = params.getString("startTime");
        String endtime = params.getString("endTime");
        String DataSource="db1";

        switch (indicatorsName){
            case "0":
                indicatorsName="%绩效%";
                break;
            case "1":
                indicatorsName="%三甲%";
                break;
            case "2":
                indicatorsName="%重点%";
                break;
            case "3":
                indicatorsName="%护理%";
                break;
            case "4":
                indicatorsName="%运营%";
                break;
            default:
                indicatorsName=indicatorsName;
        }
        List<Map<String, Object>> mapList = indicatorsDataMapper.find_keyword(searchwords,indicatorsName);
        if (mapList.size()==0){
            List<JSONObject> nullres =new ArrayList<>();
            JSONObject jsonObject = new JSONObject();
            jsonObject.put("message",indicatorsName.replace("%","") + "指标中无法找到："+searchwords+" ，请检查搜索条件");
            nullres.add(jsonObject);
            ResponseVo responseVo = new ResponseVo(ResultCode.INDICATOR_NUM_NULL.getCode(),ResultCode.INDICATOR_NUM_NULL.getMsg(),nullres);
            return responseVo;
        }

        List<String> indIdList=new ArrayList<>();
        for (int i = 0; i < mapList.size(); i++) { indIdList.add(mapList.get(i).get("ind_id").toString()); }

        JSONObject requestBody = new JSONObject();
        requestBody.put("indIdList",indIdList);
        requestBody.put("startTime",starttime);
        requestBody.put("endTime",endtime);
        requestBody.put("mqFlag",false);

        String url = String.format("%s/qxzsk/indicators/sqlDataSetList", serverConfig.getUrl());
        String responsestr = RestTemplateUtils.postss(url, requestBody);
        net.sf.json.JSONObject json = net.sf.json.JSONObject.fromObject(responsestr);
        JSONArray data = json.getJSONArray("data");
        String msg = json.getString("msg");
        List<JSONObject> jsonObjectList= (List<JSONObject>) data.toCollection(data, JSONObject.class);


        for (int i = 0; i < jsonObjectList.size(); i++) {
            for (int j = 0; j < mapList.size(); j++) {
                if (  jsonObjectList.get(i).getString("ind_id").toLowerCase().equals(mapList.get(j).get("ind_id").toString().toLowerCase())){
                    jsonObjectList.get(i).put("ind_name",mapList.get(j).get("ind_name"));
                    jsonObjectList.get(i).put("sql_total",jsonObjectList.get(i).get("sql"));
                    jsonObjectList.get(i).put("sql_detail_value",mapList.get(j).get("sql_detail_value"));
                    jsonObjectList.get(i).put("update_time", DateUtils.dateToString((Date) mapList.get(j).get("update_time")));
                    jsonObjectList.get(i).put("admin_name",mapList.get(j).get("admin_name"));
                    jsonObjectList.get(i).put("startTime",starttime);
                    jsonObjectList.get(i).put("endTime",endtime);
                    jsonObjectList.get(i).remove("sql");// sql=》sql_total
                    jsonObjectList.get(i).remove("sql_result_jsonArray");//去掉明细结果
                }
            }

        }

        ResponseVo responseVo = new ResponseVo(SUCCESS.getCode(),msg,jsonObjectList);
        return responseVo;
    }


    @ApiOperation(value = "sql结果",  notes = "")
    @PostMapping(value = "/sqlDataSet")
    @ApiImplicitParams({ @ApiImplicitParam(name = "ind_id", value = "单个指标唯一编码", required = true, dataType = "String"),
            @ApiImplicitParam(name = "flag", value = " 0单指标结果 1明细指标调试 2明细指标sql（不执行sql，仅供新建层使用）3明细指标查询全部返回 4 明细指标分页查询（需传入分页参数） ", required = true, dataType = "Integer"),
            @ApiImplicitParam(name = "dept_code", value = "科室编号(可选参数)", required = false, dataType = "String"),
            @ApiImplicitParam(name = "pageNum", value = "第几页(可选参数)", required = false, dataType = "Integer"),
            @ApiImplicitParam(name = "pageSize", value = "页大小(可选参数)", required = false, dataType = "Integer"),
            @ApiImplicitParam(name = "D1", value = "开始时间 YYYY-MM-DD 24hh:mi:ss 2020-1-01 00:00:00", required = true, dataType = "String"),
            @ApiImplicitParam(name = "D2", value = "结束时间 YYYY-MM-DD 24hh:mi:ss 2020-5-01 00:00:00", required = true, dataType = "String") })
    public   Object  sqlDataSet(@RequestParam String ind_id, Integer flag, String D1, String D2, String dept_code, Integer pageNum, Integer pageSize ) throws ParseException {
        String DataSource="db1";

        if(ind_id==null||ind_id.length()==0){
            return  new ResponseVo(ResultCode.VALIDATE_ERROR.getCode(),ResultCode.VALIDATE_ERROR.getMsg(),"ind_id为空");
        }
        List<Map<String, Object>> sqllist = indicatorsDataMapper.find_indicators_sql(ind_id);//参数为空则查询全部
        if (sqllist.size()==0){
            return new ResponseVo(ResultCode.INDICATOR_NUM_NULL.getCode(),ResultCode.INDICATOR_NUM_NULL.getMsg(),ind_id+"不存在");
        }
        JSONObject jsonObject=new JSONObject();
        String sql_result=null;
        String sql_result_detail=null;
        String run_time=null;
        String sql_result_all=null;
        String sql="";



        switch (flag){
            case 0:  /**单指标调试*/
                long starTime=System.currentTimeMillis();

                sql = sqllist.get(0).get("sql_total").toString();

                String sqlformat= sqlFormat.sqlFormat(sql,D1,D2,dept_code);

                JSONObject hystrxSqlresult = hystrixServer.exec_sql_overtime_60s(sqlformat);

                Enum<ResultCode>sql_message =(Enum<ResultCode>) hystrxSqlresult.get("sql_message");

                int code = ((ResultCode) sql_message).getCode();

                   if (code==ResultCode.SQL_SUCCESS.getCode()){
                       List<LinkedHashMap<String, Object>> linkedHashMaps= (List<LinkedHashMap<String, Object>>) hystrxSqlresult.get("sql_result");
                      if (linkedHashMaps.size()>1){
                          sql_result="sql失败：返回多行";
                      }
                      else {
                          Map<String, Object> map = linkedHashMaps.get(0);
                          Iterator it = map.entrySet().iterator() ;
                        while (it.hasNext())
                        {
                            Map.Entry entry = (Map.Entry) it.next() ;
                            Object key = entry.getKey() ;
                            Object value = entry.getValue() ;
                            if (!key.toString().toLowerCase().equals(ind_id.toLowerCase())){
                                sql_result="sql失败：指标编号与sql返回字段不一致";
                            }else {
                                sql_result=map.toString();
                            }
                        }

                      }

                   }
                   else {
                       sql_result=((ResultCode) sql_message).getMsg()+":"+hystrxSqlresult.getString("sql_message_error");
                   }


                run_time=(System.currentTimeMillis()-starTime)+"ms";//执行时间
                jsonObject.put("sql_result",sql_result);
                jsonObject.put("ind_id", ind_id  );
                jsonObject.put("sql",sqlformat );
                jsonObject.put("run_time",run_time);
                break;

            case 1:  /**明细指标调试 不返回查询结果*/
                sql=sqllist.get(0).get("sql_detail").toString();
                int detail_flag  =(Integer) sqllist.get(0).get("detail_flag");// 0为sql语句 1为存储过程名称
                if (detail_flag==0){
                    jsonObject = indicatorsService.indicatorsDetailSqlTest(sql, D1, D2, dept_code,1,1);

                }
                if (detail_flag==1){
                     jsonObject= indicatorsService.indicatorsDetailPorduceTest(sql, D1, D2, dept_code,1,1);

                }
                jsonObject.put("ind_id", ind_id );

                //成功则更新到新建层
                if (jsonObject.getString("sql_result").equals("sql成功")){
                    try {
                        String url = ymlCofig.getRegainDataUrl()+"/api/v1/qxqcsdbapi/indicators/updateIndicatorsLibraryDetailsSql";
                        MultiValueMap<String, Object> requestData=new LinkedMultiValueMap<>();
                        requestData.add("kpiCode",ind_id);
                        requestData.add("detailsSql",jsonObject.getString("sql"));
                        String resultStr = RestTemplateUtils.postFormData(url,requestData);
                        jsonObject.put("sqlUpdate",resultStr);
                    }catch (Exception e){
                    //    log.info("指标"+ind_id+"明细指标更新到新建层失败"+e.getMessage());
                        jsonObject.put("sqlUpdate","指标"+ind_id+"明细指标更新到新建层失败"+e.getMessage());
                    }


                }
                break;



            case 2:   /**明细指标,不执行sql（仅供新建层接口）*/
                sql=sqllist.get(0).get("sql_detail").toString();
                starTime=System.currentTimeMillis();
                try {
                    if (sql.length()>0){
                        sql= sqlFormat.sqlFormat(sql,D1,D2,dept_code);
                    }
                }catch (Exception e){
                    sql="";
                }

                    jsonObject.put("ind_id", ind_id  );
                    jsonObject.put("sql",sql);
                    jsonObject.put("run_time", (System.currentTimeMillis()-starTime)+"ms");//执行时间);
                break;

            case 3:   /** 明细查询 全部返回明细数据 不分页 */
                sql=sqllist.get(0).get("sql_detail").toString();
                detail_flag  =(Integer) sqllist.get(0).get("detail_flag");// 0为sql语句 1为存储过程名称
                if (detail_flag==0){
                    jsonObject = indicatorsService.indicatorsDetailSqlNoPage(sql, D1, D2, dept_code);
                }
                if (detail_flag==1){
                    jsonObject= indicatorsService.indicatorsDetailPorduceNoPage(sql, D1, D2, dept_code);

                }
                jsonObject.put("ind_id", ind_id );
                break;

            case 4:  /**明细指标分页查询 需传入分页参数*/
                sql=sqllist.get(0).get("sql_detail").toString();
                detail_flag  =(Integer) sqllist.get(0).get("detail_flag");// 0为sql语句 1为存储过程名称
                if (detail_flag==0){
                    jsonObject = indicatorsService.indicatorsDetailSql(sql, D1, D2, dept_code,pageNum,pageSize);
                }
                if (detail_flag==1){
                    jsonObject= indicatorsService.indicatorsDetailPorduce(sql, D1, D2, dept_code,pageNum,pageSize);
                }
                jsonObject.put("ind_id", ind_id );
                break;

            default:
                 return jsonObject;
        }
        return jsonObject;

    }

    @ApiOperation(value = "导出excel",  notes = "")
    @RequestMapping(value = "/downloadExcel", method= RequestMethod.GET )
    @ApiImplicitParams({ @ApiImplicitParam(name = "ind_id",    value = "指标编码", required = false, dataType = "String"),
                         @ApiImplicitParam(name = "D1",        value = "开始日期 ", required = false, dataType = "String"),
                         @ApiImplicitParam(name = "D2",        value = "开始日期", required = false, dataType = "String"),
                         @ApiImplicitParam(name = "dept_code", value = "科室编码", required = false, dataType = "String"),
                         @ApiImplicitParam(name = "sql",       value = "sql语句", required = false, dataType = "String"),
                         @ApiImplicitParam(name = "isZip",       value = "是否导出压缩", required = false, dataType = "boolean"),

                       })

    public Object downloadExcel( String ind_id,  String D1, String D2, String dept_code,String sql,boolean isZip,  HttpServletResponse response) throws Exception{
        List<Map<String, Object>> mapList=  new ArrayList<>();




       //自定义sql导出
        if(sql!=null){
          String  excelName= "sqlResultToExcel";
          downloadExcelServer.downloadExcelSDQuery(excelName,sql,isZip,1000000);

       //     ResponseVo responseVo = new ResponseVo(SUCCESS.getCode(),SUCCESS.getMsg(),jsonObjectList);
            return null;
        }


        if(ind_id==null||ind_id.length()==0){
            return  new ResponseVo(ResultCode.VALIDATE_ERROR.getCode(),ResultCode.VALIDATE_ERROR.getMsg(),"ind_id为空");
        }
        List<Map<String, Object>> sqllist = indicatorsDataMapper.find_indicators_sql(ind_id);//参数为空则查询全部
        if (sqllist.size()==0){
            return new ResponseVo(ResultCode.INDICATOR_NUM_NULL.getCode(),ResultCode.INDICATOR_NUM_NULL.getMsg(),ind_id+"不存在");
        }


        String   sql_detail=sqllist.get(0).get("sql_detail").toString();
        String excelName=sqllist.get(0).get("ind_name").toString();

       int  detail_flag  =(Integer) sqllist.get(0).get("detail_flag");// 0为sql语句 1为存储过程名称

        // 0为sql语句 采取流查询导出excel
        if (detail_flag==0){
            String  sqlformat= sqlFormat.sqlFormat(sql_detail,D1,D2,dept_code);
            downloadExcelServer.downloadExcelSDQuery(excelName,sqlformat,isZip,500000);
        }


        // 1为存储过程名称时候 不能通过流查询导出excel 采取另外方式
        if (detail_flag==1){
            Map<String, Object> map = new HashMap<>();
            map.put("D1", D1);
            map.put("D2", D2);
            map.put("produceName", sql_detail);
            produceMapper.produce(map);
            mapList =( List<Map<String, Object>> ) map.get("p_cur");

            response.setContentType("application/vnd.ms-excel;charset=UTF-8");
            response.setHeader("content-disposition","attachment;filename="+new String((excelName+".xlsx").getBytes("gb2312"), "ISO8859-1"));
            OutputStream outputStream = response.getOutputStream();
            downloadExcelServer.downloadExcel(mapList,excelName,outputStream);

        }

       return  null;
    }

//
//    @PostMapping("/upload")
//    public Object upload(  MultipartFile file){
//        try {
//            if (file.isEmpty()) {
//                return  null;
//            } else {
//
//                byte[] bytes = file.getBytes();
//                InputStream inputStream= new ByteArrayInputStream(bytes);
//
//                ExcelLogs logs =new ExcelLogs();
//                Collection<Map> importExcel = ExcelUtil.importExcel(Map.class, inputStream, "yyyy/MM/dd HH:mm:ss", logs , 0);
//
//                for(Map m : importExcel){
//                    System.out.println(m);
//                }
//             return  importExcel;
//
//            }
//        } catch (Exception ex) {
//
//            //记录日志
//            return  null;
//        }
//
//
//    }






}



